﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Reflection;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using OrionApplication.OrionLib.Dao;
using System.IO;

namespace OrionApplication.OrionLib.Orion
{
    public class OrionOutilsBase
    {
        /// <summary>
        /// Renvoi un String représentant la valeur de la clef passée en paramétre.
        /// Si cette clef n'existe pas, renvoie String.Empty
        /// </summary>
        /// <param name="key">La clef du fichier de configuration</param>
        /// <returns>La valeur représentée dans le fichier de configuration de la clef</returns>
        public static string GetValueFileConfiguration(string key)
        {
            string retour = string.Empty;
            if (ConfigurationManager.AppSettings.AllKeys.Contains(key))
            {
                retour = ConfigurationManager.AppSettings[key].ToString();
            }
            return retour;
        }

        /// <summary>
        /// Permet de Logguer l'insertion d'une requête 
        /// </summary>
        /// <param name="req"></param>
        public static void Log(string req)
        {
            OrionLog ol = new OrionLog();
            ol.Requete = req;

            DaoOrionLog daoOL = new DaoOrionLog();
            daoOL.Insert(ol);
        }

        /// <summary>
        /// Permet de Logguer l'insertion d'une requête 
        /// </summary>
        /// <param name="req"></param>
        public static void Log(string req, List<MySqlParameter> lParam)
        {
            OrionLog ol = new OrionLog();
            ol.Requete = req + System.Environment.NewLine;

            foreach (MySqlParameter p in lParam)
            {
                ol.Requete += p.ParameterName + "=" + System.Convert.ToString(p.Value) + System.Environment.NewLine;
            }

            DaoOrionLog daoOL = new DaoOrionLog();
            daoOL.Insert(ol);
        }

        /// <summary>
        /// Calcul la Clef RIB selon les parametres
        /// </summary>
        /// <param name="codeBanque">Le code banque</param>
        /// <param name="codeGuichet">Le code guichet</param>
        /// <param name="numCompte">Le numero de compte</param>
        /// <returns>La clef RIB</returns>
        public static string DonneCleRIB(string codeBanque, string codeGuichet, string numCompte)
        {
            numCompte = numCompte.Replace("A", "1").Replace("J", "1")
                                 .Replace("B", "2").Replace("K", "2").Replace("S", "2")
                                 .Replace("C", "3").Replace("L", "3").Replace("T", "3")
                                 .Replace("D", "4").Replace("M", "4").Replace("U", "4")
                                 .Replace("E", "5").Replace("N", "5").Replace("V", "5")
                                 .Replace("F", "6").Replace("O", "6").Replace("W", "6")
                                 .Replace("G", "7").Replace("P", "7").Replace("X", "7")
                                 .Replace("H", "8").Replace("Q", "8").Replace("Y", "8")
                                 .Replace("I", "9").Replace("R", "9").Replace("Z", "9");
            int b = int.Parse(codeBanque);
            int g = int.Parse(codeGuichet);
            int c = int.Parse(numCompte);

            int k = 97 - ((89 * b + 15 * g + 3 * c) % 97);

            return k.ToString("00");
        }

        /// <summary>
        /// Retouner le contrôle dont le nom est précisé en paramétre
        /// </summary>
        /// <param name="sender"></param>
        /// <returns></returns>
        public static Control GetControlsAvecRecursivite(Control sender, String ControleName)
        {
            Control retour = new Control();
            IEnumerable<Control> controls = OrionOutilsBase.ControlsAvecRecursivite(sender);
            // Lister les controls enfant
            foreach (Control c in controls)
            {
                if (c.Name == ControleName)
                {
                    retour = c;
                    break;
                }
            }
            return retour;
        }

        /// <summary>
        /// Retouner une liste de tous les controls contenus dans un control sans oublier les controls enfant de ceux-ci
        /// </summary>
        /// <param name="sender"></param>
        /// <returns></returns>
        public static IEnumerable<Control> ControlsAvecRecursivite(Control sender)
        {
            // Lister les controls enfant
            foreach (Control c in sender.Controls)
            {
                // Retourner le control enfant
                yield return c;
                // Pour chague control retourner ses controls enfant via l'énumération [Recursivite]
                foreach (Control child in ControlsAvecRecursivite(c))
                {
                    yield return child;
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tabF"></param>
        /// <param name="nameForm"></param>
        /// <returns></returns>
        public static bool IsFormAlReadyOpen(Form[] tabF, string nameForm)
        {
            foreach (Form f in tabF)
            {
                if (f.Name == nameForm)
                    return true;
            }
            return false;
        }

        /// <summary>
        /// Exports a passed datagridview to an Excel worksheet.
        /// If captions is true, grid headers will appear in row 1.
        /// Data will start in row 2.
        /// </summary>
        /// <param name="kryptonDataGridView">Le kryptonDataGridView qui doit-être exporté</param>
        /// <param name="captions">Doit-on afficher ou non les noms de colonne</param>
        public static void Export2Excel(ComponentFactory.Krypton.Toolkit.KryptonDataGridView kryptonDataGridView, bool captions)
        {
            object objApp_Late;
            object objBook_Late;
            object objBooks_Late;
            object objSheets_Late;
            object objSheet_Late;
            object objRange_Late;
            object[] Parameters;
            string[] headers = new string[kryptonDataGridView.ColumnCount];
            string[] columns = new string[kryptonDataGridView.ColumnCount];

            int i = 0;
            int c = 0;
            for (c = 0; c < kryptonDataGridView.ColumnCount; c++)
            {
                headers[c] = kryptonDataGridView.Rows[0].Cells[c].OwningColumn.Name.ToString();
                i = c + 65;
                columns[c] = Convert.ToString((char)i);
            }

            try
            {
                // Get the class type and instantiate Excel.
                Type objClassType;
                objClassType = Type.GetTypeFromProgID("Excel.Application");
                objApp_Late = Activator.CreateInstance(objClassType);
                //Get the workbooks collection.
                objBooks_Late = objApp_Late.GetType().InvokeMember("Workbooks", BindingFlags.GetProperty, null, objApp_Late, null);
                //Add a new workbook.
                objBook_Late = objBooks_Late.GetType().InvokeMember("Add", BindingFlags.InvokeMethod, null, objBooks_Late, null);
                //Get the worksheets collection.
                objSheets_Late = objBook_Late.GetType().InvokeMember("Worksheets", BindingFlags.GetProperty, null, objBook_Late, null);
                //Get the first worksheet.
                Parameters = new Object[1];
                Parameters[0] = 1;
                objSheet_Late = objSheets_Late.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, objSheets_Late, Parameters);

                if (captions)
                {
                    // Create the headers in the first row of the sheet
                    for (c = 0; c < kryptonDataGridView.ColumnCount; c++)
                    {
                        //Get a range object that contains cell.
                        Parameters = new Object[2];
                        Parameters[0] = columns[c] + "1";
                        Parameters[1] = Missing.Value;
                        objRange_Late = objSheet_Late.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, objSheet_Late, Parameters);
                        //Write Headers in cell.
                        Parameters = new Object[1];
                        Parameters[0] = headers[c];
                        objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objRange_Late, Parameters);
                    }
                }

                // Now add the data from the grid to the sheet starting in row 2
                for (i = 0; i < kryptonDataGridView.RowCount; i++)
                {
                    for (c = 0; c < kryptonDataGridView.ColumnCount; c++)
                    {
                        //Get a range object that contains cell.
                        Parameters = new Object[2];
                        Parameters[0] = columns[c] + Convert.ToString(i + 2);
                        Parameters[1] = Missing.Value;
                        objRange_Late = objSheet_Late.GetType().InvokeMember("Range", BindingFlags.GetProperty, null, objSheet_Late, Parameters);
                        //Write Headers in cell.
                        Parameters = new Object[1];
                        Parameters[0] = kryptonDataGridView.Rows[i].Cells[headers[c]].Value.ToString();
                        objRange_Late.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, objRange_Late, Parameters);
                    }
                }

                //Return control of Excel to the user.
                Parameters = new Object[1];
                Parameters[0] = true;
                objApp_Late.GetType().InvokeMember("Visible", BindingFlags.SetProperty, null, objApp_Late, Parameters);
                objApp_Late.GetType().InvokeMember("UserControl", BindingFlags.SetProperty, null, objApp_Late, Parameters);
            }
            catch (Exception theException)
            {
                String errorMessage;
                errorMessage = "Error: ";
                errorMessage = String.Concat(errorMessage, theException.Message);
                errorMessage = String.Concat(errorMessage, " Line: ");
                errorMessage = String.Concat(errorMessage, theException.Source);

                MessageBox.Show(errorMessage, "Error");
            }
        }

        /// <summary>
        /// Donne la liste des tables mais pas les vues
        /// </summary> 
        /// <returns>List<string> Liste des tables</returns>
        public static List<string> GetListTable()
        {
            List<string> retour = new List<string>();
            string req = "SHOW FULL TABLES WHERE Table_type = 'BASE TABLE';";
            DaoBase dao = new DaoBase();
            MySqlDataReader dr = dao.ExecuteQueryReader(req);

            while(dr.Read())
                retour.Add(dr.GetString(0));
            dr.Close();
            return retour;
        }

        public static List<OrionColonne> GetListColumn(string table)
        {
            List<OrionColonne> retour = new List<OrionColonne>();
            string req = "SHOW COLUMNS FROM " + table;
            DaoBase dao = new DaoBase();
            MySqlDataReader dr = dao.ExecuteQueryReader(req);

            while (dr.Read())
            {
                if (!dr.GetString("Field").Equals("OrionId") &&
                    !dr.GetString("Field").Equals("OrionEtat") &&
                    !dr.GetString("Field").Equals("OrionUserId") &&
                    !dr.GetString("Field").Equals("OrionCrea") &&
                    !dr.GetString("Field").Equals("OrionMaj"))
                {
                    OrionColonne tCol = new OrionColonne();
                    tCol.Nom = dr.GetString("Field");
                    tCol.Type = dr.GetString("Type");
                    if(tCol.Type.ToUpper().Contains("TINYINT(1)"))
                        tCol.Type = "Boolean";
                    else if (tCol.Type.ToUpper().Contains("INT("))
                        tCol.Type = "Int32";
                    else if (tCol.Type.ToUpper().Contains("DATETIME"))
                        tCol.Type = "DateTime";
                    else if (tCol.Type.ToUpper().Contains("VARCHAR")
                    || tCol.Type.ToUpper().Contains("TEXT"))
                        tCol.Type = "String";
                    else if (tCol.Type.ToUpper().Contains("DOUBLE"))
                        tCol.Type = "Double";

                    retour.Add(tCol);
                }
            }
            dr.Close();
            return retour;
        }
    }
}
